package remoting
{

	//-------------------------------------------------------------------------------------------------------
	//
	//	Singleton gérant la connexion à la base de données locale (SQLite)
	//
	//-------------------------------------------------------------------------------------------------------
	
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.filesystem.File;
	
	import org.osmf.logging.Logger;
	
	public class LocalConnection extends Logger
	{
		private static var constructorKey:Object = {};
		private static var instance:LocalConnection = null;
		
		private var databaseFile:File;
		
		public function LocalConnection(pConstructorKey:Object)
		{
			if(pConstructorKey != constructorKey)
			{
				throw new Error("Instanciation illégale (constructeur privé)");
			}
			
			databaseFile = File.userDirectory.resolvePath("base.sqlite");
			
		}
		
		private function executeQuery(pMode:String, pQuery:String, pParameters:Object = null, pClass:Class=null):SQLResult
		{
			try
			{
				var Connection:SQLConnection = new SQLConnection();
				var Statement:SQLStatement = new SQLStatement();
				
				Connection.open(databaseFile, pMode);
				
				Statement.sqlConnection = Connection;
				
				Statement.text = pQuery;
				if(pParameters != null)
				{
					for(var id:String in pParameters)
					{
						Statement.parameters[id] = pParameters[id];
					}
				}
				if(pClass != null)
				{
					Statement.itemClass = pClass;
				}
				Statement.execute();
				
				return Statement.getResult();
			}
			catch(error:SQLError)
			{
				trace("Requête : <" + pQuery + ">\n" + error.toString());
			}
			finally 
			{
				Connection.close();
			}
			return null;
		}
		
		public static function get Instance():LocalConnection
		{
			if(instance == null)
			{
				instance = new LocalConnection(constructorKey);
			}
			return instance;
		}
		
		public function createDatabase():Boolean
		{
			if(databaseFile.exists)
			{
				return true;
			}
			
			var sQuery:String = "CREATE TABLE joueur (";
			
			sQuery += "ID INTEGER PRIMARY KEY, "
			sQuery += "Pseudo VARCHAR(50), "
			sQuery += ")";
			
			var sQuery2:String = "INSERT INTO joueur (id) VALUES (1);"
			
			var result:SQLResult = this.executeQuery(SQLMode.CREATE, sQuery);
			var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery2);
			
			var sQuery:String = "CREATE TABLE joueur_jeu (";
			
			sQuery += "joueur_ID INTEGER PRIMARY KEY, "
			sQuery += "jeu_id INTEGER, "
			sQuery += "nbPoints INTEGER, "
			sQuery += ")";
			
			var result:SQLResult = this.executeQuery(SQLMode.CREATE, sQuery);
			
			var sQuery:String = "CREATE TABLE jeu (";
			
			sQuery += "ID INTEGER PRIMARY KEY, "
			sQuery += "episode INTEGER, "
			sQuery += "type INTEGER, "
			sQuery += ")";
			
			var sQuery2:String = "INSERT INTO jeu (id, episode, type) VALUES (1, 1, 1)";
			
			var sQuery3:String = "INSERT INTO jeu (id, episode, type) VALUES (1, 1, 2)";
			
			var result:SQLResult = this.executeQuery(SQLMode.CREATE, sQuery);
			var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery2);
			var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery3);
			
			return result != null;
		}
		
		public function savePoints(nbpoints:int,id_jeu:int):Boolean
		{
			var sQuery:String = "INSERT INTO joueur_jeu (joueur_id, jeu_id, nbPoints) VALUES (1, @id_jeu, @nbPoints)";
			var params:Object = {"@nbPoints" : nbpoints, "@id_jeu" : id_jeu};
			
			var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery, params);
			
			return result != null;
			
		}
		
		public function getPoints(id_jeu:int):int
		{
			var sQuery:String = "SELECT nbPoints FROM joueur_jeu WHERE jeu_id=@id_jeu AND joueur_id=1";
			var params:Object = {"@id_jeu" : id_jeu};
			
			var result:SQLResult = this.executeQuery(SQLMode.UPDATE, sQuery, params);
			
			if(result != null)
			{
				return result.data == null ? new Array() : result.data;
			}
			return null;
		}
	
	}
}